import 'package:sqflite/sqflite.dart';

abstract class DBStorage {
  static const String _dbName = "test"; // 数据库名称
  static const int _version = 1; // 数据库版本号
  static late String _dbBasePath; // 数据库路径

  static late Database _db; // 数据库实例
  ///监听到数据库升级时的操作
  static onUpgrade(Database db, int oldVersion, int newVersion) {}

  ///数据库降级的操作
  static onDowngrade(Database db, int oldVersion, int newVersion) {}

  ///数据库实例化完成后的操作
  static onReload(Database db, int version) {}

  ///表名称
  abstract String tableName; // 表名称

  ///表是否存在
  bool exists = false;

  ///创建表
  Future<void> onCreate(Database db, int version);
  DBStorage() {
    print("创建表--父类");
    _initTable();
  }

  ///创建数据库
  static Future<Database> initDatabase() async {
    print("打开数据库");
    _dbBasePath = "${await getDatabasesPath()}/$_dbName.db";

    print(await getDatabasesPath());
    // 打开数据库
    _db = await openDatabase(
      _dbBasePath,
      version: _version,
      // onConfigure: (db) { },
      // onCreate: onCreate,
      onUpgrade: (db, old, newV) {
        onUpgrade(db, old, newV);
        print("数据库升级了");
      },
      onDowngrade: (db, old, newV) {
        onDowngrade(db, old, newV);
        print("数据库降级了");
      },
      // onOpen: onOpen,
    );
    onReload(_db, _version);
    return _db;
  }

  ///创建表
  Future<void> _initTable() async {
    //内建表sqlite_master
    var res = await _db.rawQuery(
      "SELECT * FROM sqlite_master WHERE TYPE = 'table' AND NAME = '$tableName'",
    );
    if (res.isEmpty) {
      await onCreate(_db, _version);
    }
    exists = true;
  }

  ///表列是否存在
  Future<bool> columnExists(String columnName) async {
    var result = await _db.rawQuery("""
      SELECT *
      FROM sqlite_master
      WHERE type = 'table' AND name='$tableName' AND sql LIKE '%$columnName%';
    """);
    return result.isNotEmpty;
  }

  ///新增列
  Future addColumn(String columnName, String type) async {
    return await _db.rawQuery("""
      ALTER TABLE $tableName ADD  $columnName $type
    """);
  }

  ///删表
  Future<void> dropTable() async {
    return await _db.execute("""
      drop table if exists $tableName;
    """);
  }

  ///插入数据
  Future<int> insert(dynamic data) async {
    return _db.insert(tableName, data.toJson());
  }

  ///删除数据, 使用whereArgs预防sql注入
  Future<int> delete(Map<String, Object?> where) async {
    List<String> keys = where.keys.toList();
    List<Object?> whereArgs = where.values.toList();
    return _db.delete(
      tableName,
      // 条件
      where: keys.isEmpty ? null : "${keys.join(" = ? AND ")} = ?",
      // 值
      whereArgs: whereArgs,
    );
  }

  ///修改数据
  Future<int> update(Map<String, Object?> where, dynamic data) async {
    List<String> keys = where.keys.toList();
    List<Object?> whereArgs = where.values.toList();
    return _db.update(
      tableName,
      data.toJson(),
      where: keys.isEmpty ? null : "${keys.join(" = ? AND ")} = ?",
      whereArgs: whereArgs,
    );
  }

  ///查找所有
  Future<List<dynamic>> findAll() async {
    return await _db.query(tableName);
  }

  ///条件查询
  Future<List<dynamic>> findBy(Map<String, Object?> where) async {
    List<String> keys = where.keys.toList();
    List<Object?> whereArgs = where.values.toList();
    return await _db.query(
      tableName,
      where: keys.isEmpty ? null : "${keys.join(" = ? AND ")} = ?",
      whereArgs: whereArgs,
    );
  }

  ///条件翻页查询
  Future<List<dynamic>> findPage({
    Map<String, Object?>? where,
    int page = 1,
    int pageSize = 20,
  }) async {
    List<String> keys = where?.keys.toList() ?? [];
    List<Object?> whereArgs = where?.values.toList() ?? [];
    return await _db.query(
      tableName,
      where: keys.isEmpty ? null : "${keys.join(" = ? AND ")} = ?",
      whereArgs: whereArgs,
      offset: (page - 1) * pageSize,
      limit: pageSize,
    );
  }

  rawQuery(String sql) async {
    return _db.rawQuery(sql);
  }
}
